package auctioncentral;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import org.sql2o.Sql2o;


/**
 * Class that contains database functionality.
 * 
 * @author Kyle Scharnhorst
 * @version 0.1
 */
public class Database {

  /**
  * name of the database.
  */
  private static final String DATABASE = "auctions";
    
  /**
   * Port to connect to the database.
   */
  private static final String PORT = "3306";
  
  /**
   * user name used to connect to the database.
   */
  private static final String USERNAME = "tcss360";
  
  /**
   * host address to connect to the database.
   */
  private static final String HOST = "71.19.151.5"; 
  
  /**
   * password to use to connect to the database.
   */
  private static final String PASSWORD = "123456";
    
    /**
     * The type of database.
     */
  private static Sql2o sql;

  /**
   * Constructs this object with the necessary values.
   */
  public Database() {
    connect();
    isConnected();
  }

  /**
   * Gets the non profit user stored in the database.
   * <dt><b>Precondition:</b><dd> The connection to the database was successful.
   * <dt><b>Postcondition:</b><dd> All of the non profit users are in a list.
   * @return The list of non profit users.
   */
  public List<AuthNonProfitUser> retrieveNonProfitUsers() {
    
    List<AuthNonProfitUser> return_list = new ArrayList<>();
    
  //load data into helper objects
    List<NonProfitDatabaseHelper> ls =  sql.createQuery("SELECT the_name, the_add, the_num, my_current_event FROM AuthNonProfitUser")
        .executeAndFetch(NonProfitDatabaseHelper.class);
    
    //build objects
    for (int i = 0; i < ls.size(); i++) {
      NonProfitDatabaseHelper np = ls.get(i);
      return_list.add(new AuthNonProfitUser(np.getThe_name(), np.getThe_add(), np.getThe_num(), np.getMy_current_event()));
    }
    
    return return_list;
    
  }
    
  /**
   * Identifies if the database was connected.
   * 
   * <dt><b>Precondition:</b><dd> the connected method was called beforehand. 
   * <dt><b>Postcondition:</b><dd> the status of the connection will be returned.
   * @return true if the connection to the database was established and false otherwise.
   */
    public static boolean isConnected() {
        boolean isConnected = false;
        try {
            sql.getDataSource()
                         .getConnection();
            isConnected = true;
        }
        catch (SQLException exception) {
            exception.printStackTrace();
        }
        return isConnected;
    }
    
   /**
    *  This method establishes a connection to the database using the sql2o object and static strings.
    *  <dt><b>Precondition:</b><dd> the static strings need to have the correct values so the object
    *  establish a connection to the database.
    *  <dt><b>Postcondition:</b><dd> The sql2o will have been initialized. It is not possible to call isConnected()
    *  to see if a connection was established. 
    */
	private static void connect() {
		sql = new Sql2o("jdbc:mysql://" + HOST + ":" + PORT + "/" + DATABASE + "?zeroDateTimeBehavior=convertToNull", USERNAME, PASSWORD);
	}
  
	/**
	 * This method gets a list of Event like object that are used to translate into.
	 * <dt><b>Precondition:</b><dd> Connection to the database was successful.
	 * <dt><b>Postcondition:</b><dd> A list of helper object have been created and returned.
	 * @return the list of helper objects.
	 */
  public List<EventDatabaseHelper> retrieveEventDatabaseHelpers() {
    
    return sql.createQuery("SELECT my_name, my_user, my_contact_num, my_intake_user, my_year, my_month, "
        + "my_day, my_hour, my_minute, my_duration, my_est_items, my_comments, my_approval_status FROM Event")
        .executeAndFetch(EventDatabaseHelper.class);
  }

  /**
   * This method stores the current list of events into the database to be stored as
   * persistent data.
   *  <dt><b>Precondition:</b><dd> There are Events in the list. That is, not null.
   *  <dt><b>Postcondition:</b><dd> Every Event in the list will now be in the database.
   * @param some_event_list A list of Event objects.
   */
  public void updateEventTable(List<Event> some_event_list) {
    //variables for translating Event objects into basic data types so storage in 
    //the database is possible.
    int year = 0;
    int month = 0;
    int day = 0;
    int hour = 0;
    int min = 0;
    String approval_status = "";
    String user = "";
    String intake = "";
    
    //deletes the entries in the event table
    sql.createQuery("DELETE FROM Event").executeUpdate();
    
    //rebuilds that table with every object in some list.
    for(Event e : some_event_list){
      
      //sets up the calendar object to be stored in the database as primitive types.
      year = e.getDate().get(Calendar.YEAR);
      month = e.getDate().get(Calendar.MONTH);
      day = e.getDate().get(Calendar.DAY_OF_MONTH);
      hour = e.getDate().get(Calendar.HOUR_OF_DAY);
      min = e.getDate().get(Calendar.MINUTE);
      
      //converts boolean value to a string
      if(e.getMyApprovalStatus()) {
        approval_status = "true";
      } else {
        approval_status = "false";
      }
      
      //converts null into empty string
      if(e.getUser() == null) {
        user = "";
      } else {
        user = e.getUser();
      }
      
      //converts null into empty string
      if(e.getIntakeUser() == null) {
        intake = "";
      } else {
        intake = e.getIntakeUser();
      }
      
      //code that inserts the objects into the database.
      sql.createQuery("INSERT INTO Event(my_name, my_user, my_contact_num, my_intake_user, my_year, my_month, "
        + "my_day, my_hour, my_minute, my_duration, my_est_items, my_comments, my_approval_status) VALUES "
        + "(:name_param, :user_param, :contact_num_param, :intake_user_param, :year_param, :month_param, "
        + ":day_param, :hour_param, :minute_param, :duration_param, :est_items_param, :comments_param, "
        + ":approval_status)")
        .addParameter("name_param", e.getName())
        .addParameter("user_param", user)
        .addParameter("contact_num_param", e.getContactNumber())
        .addParameter("intake_user_param", intake)
        .addParameter("year_param", year)
        .addParameter("month_param", month)
        .addParameter("day_param", day)
        .addParameter("hour_param", hour)
        .addParameter("minute_param", min)
        .addParameter("duration_param", e.getDuration())
        .addParameter("est_items_param", e.getEstItems())
        .addParameter("comments_param", e.getComments())
        .addParameter("approval_status", approval_status)
        .executeUpdate();
      
    }
    
  }

  /**
   * This method retrieves auction central staff users from the database.
   *  <dt><b>Precondition:</b><dd> The connection to the database was established.
   *  <dt><b>Postcondition:</b><dd> A list of auction central staff object have been
   *  created and returned.
   * @return The list of auction central staff users.
   */
  public List<AuCenStaffUser> retrieveAuctionCentralStaffUsers() {
    
    List<AuCenStaffUser> return_list = new ArrayList<>();
    
    //load data into helper objects
    List<AuCenStaffUserHelper> ls =  sql.createQuery("SELECT the_name FROM AuCenStaffUser")
        .executeAndFetch(AuCenStaffUserHelper.class);
    
    //build objects
    for (int i = 0; i < ls.size(); i++) {
      AuCenStaffUserHelper np = ls.get(i);
      return_list.add(new AuCenStaffUser(np.getMyName()));
    }
    return return_list;
  }

  /**
   * This method retrieves bidder users from the database.
   *  <dt><b>Precondition:</b><dd> The connection to the database was established.
   *  <dt><b>Postcondition:</b><dd> A list of bidder objects have been
   *  created and returned.
   * @return The list of bidder users. 
   */
  public List<BiddingUser> retrieveBidderUsers() {
    
    List<BiddingUser> return_list = new ArrayList<>();
    
    //load data into helper objects
    List<BiddingUserHelper> ls =  sql.createQuery("SELECT the_name, the_address, the_phone_number, "
        + "the_card_number, the_billing_address FROM BiddingUser")
        .executeAndFetch(BiddingUserHelper.class);
  
    //build objects
    for (int i = 0; i < ls.size(); i++) {
      BiddingUserHelper np = ls.get(i);
      return_list.add(new BiddingUser(np.getMy_name(), np.getMy_address(), np.getMy_phone_number(),
          np.getMy_card_number(), np.getMy_billing_address()));
    }
    return return_list;
  }

  /**
   * This method retrieves Items from the database.
   *  <dt><b>Precondition:</b><dd> The connection to the database was established.
   *  <dt><b>Postcondition:</b><dd> A list of Item objects have been
   *  created and returned.
   * @return The list of Items. 
   */
  public List<Item> retrieveItems() {
    
    List<Item> return_list = new ArrayList<>();
  
    //load data into helper objects
    List<ItemHelper> ls =  sql.createQuery("SELECT my_name, my_id, my_quantity, "
        + "the_event, my_min_starting_bid, my_donor, my_size, my_storage, my_condition, my_comments FROM Item")
        .executeAndFetch(ItemHelper.class);
  
    //build objects
    for (int i = 0; i < ls.size(); i++) {
      ItemHelper np = ls.get(i);
      return_list.add(new Item(np.getMy_name(), np.getMy_quantity(), np.getMy_event(),np.getMy_id(), 
          np.getMy_min_starting_bid(), np.getMy_donor(), np.getMy_size(), np.getMy_storage(), 
          np.getMy_condition(), np.getMy_comments()));
    }
    return return_list;
  }

  /**
   * This method retrieves Bids from the database.
   *  <dt><b>Precondition:</b><dd> The connection to the database was established.
   *  <dt><b>Postcondition:</b><dd> A list of Bid objects have been
   *  created and returned.
   * @return The list of Bids. 
   */
  public List<Bid> retrieveBids() {
    
    List<Bid> return_list = new ArrayList<>();

    //load data into helper objects
    List<BidHelper> ls =  sql.createQuery("SELECT my_amount, my_bidder, my_item_id FROM Bid")
        .executeAndFetch(BidHelper.class);
    
  //build objects
    for (int i = 0; i < ls.size(); i++) {
      BidHelper np = ls.get(i);
      return_list.add(new Bid(np.getMy_amount(), np.getMy_bidder(), np.getMy_item_id()));
    }
    return return_list;
  }

  /**
   * This method stores the current list of Items into the database to be stored as
   * persistent data.
   *  <dt><b>Precondition:</b><dd> There are Items in the list. That is, not null.
   *  <dt><b>Postcondition:</b><dd> Every Item in the list will now be in the database.
   * @param some_event_list A list of Item objects.
   */
  public void updateItemTable(List<Item> some_items_list) {
    sql.createQuery("DELETE FROM Item").executeUpdate();
    
    //for every item
    for(Item e : some_items_list){
      
      //insert into the database
      sql.createQuery("INSERT INTO Item(my_name, my_id, my_quantity, "
        + "the_event, my_min_starting_bid, my_donor, my_size, my_storage, my_condition, my_comments) VALUES "
        + "(:name_param, :id_param, :quantity_param, :event_param, :starting_bid_param, :donor_param, :size_param, "
        + ":storage_param, :condition_param, :comment_param)")
        .addParameter("name_param", e.getName())
        .addParameter("id_param", e.getMy_id())
        .addParameter("quantity_param", e.getQuantity())
        .addParameter("event_param", e.getMy_event())
        .addParameter("starting_bid_param", e.getMy_min_starting_bid())
        .addParameter("donor_param", e.getMy_donor())
        .addParameter("size_param", e.getMy_size())
        .addParameter("storage_param", e.getMy_storage())
        .addParameter("condition_param", e.getMy_condition())
        .addParameter("comment_param", e.getMy_comments())
        .executeUpdate();
      
    }
    
  }

  /**
   * This method stores the current list of Bid into the database to be stored as
   * persistent data.
   *  <dt><b>Precondition:</b><dd> There are Bids in the list. That is, not null.
   *  <dt><b>Postcondition:</b><dd> Every Bid in the list will now be in the database.
   * @param some_event_list A list of Bid objects.
   */
  public void updateBidTable(List<Bid> some_bid_list) {
    sql.createQuery("DELETE FROM Bid").executeUpdate();
    
    //for every bid
    for(Bid e : some_bid_list){
      
      //insert into the database
      sql.createQuery("INSERT INTO Bid(my_amount, my_bidder, my_item_id) VALUES "
        + "(:amount_param, :bidder_param, :item_id_param)")
        .addParameter("amount_param", e.getAmount())
        .addParameter("bidder_param", e.getBidder())
        .addParameter("item_id_param", e.getItemId())
        .executeUpdate();
      
    }
  }
    
    /**
     * This method stores the current list of Bid into the database to be stored as
     * persistent data.
     *  <dt><b>Precondition:</b><dd> There are Bids in the list. That is, not null.
     *  <dt><b>Postcondition:</b><dd> Every Bid in the list will now be in the database.
     * @param some_event_list A list of Bid objects.
     */
    public void updateNonProfit(List<AuthNonProfitUser> some_non_profit_list) {
      sql.createQuery("DELETE FROM AuthNonProfitUser").executeUpdate();
      
      //for every bid
      for(AuthNonProfitUser e : some_non_profit_list){
        
        //insert into the database
        sql.createQuery("INSERT INTO AuthNonProfitUser(the_name, the_add, the_num, my_current_event) VALUES "
          + "(:name_param, :address_param, :num_param, :current_event_param)")
          .addParameter("name_param", e.getName())
          .addParameter("address_param", e.getAddress())
          .addParameter("num_param", e.getPhoneNo())
          .addParameter("current_event_param", e.getCurrEvent())
          .executeUpdate();
        
      }
    
  }


}
